package com.slashmanx.goals2u;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class GoalsDatabaseManager {
	
	private SQLiteDatabase db; // a reference to the database manager class.
	private final String DB_NAME = "goals2u"; // the name of our database
	private final int DB_VERSION = 3; // the version of the database
 
	// the names for our database columns
	private final String TABLE_NAME 			= "matches";
	private final String TABLE_ROW_ID 			= "id";
	private final String TABLE_ROW_HOME_TEAM 	= "homeTeam";
	private final String TABLE_ROW_AWAY_TEAM 	= "awayTeam";
	private final String TABLE_ROW_HOME_SCORE 	= "homeScore";
	private final String TABLE_ROW_AWAY_SCORE 	= "awayScore";
	private final String TABLE_ROW_MINUTE 		= "minute";
	private final String TABLE_ROW_TYPE 		= "type";
	private final String TABLE_ROW_LEAGUE 		= "league";
	private Context context;
	private MatchSQLiteDBHelper helper;
 
    // TODO: write the constructor and methods for this class
	
	public GoalsDatabaseManager(Context context)
	{
		this.context = context;
 
		// create or open the database

		Log.v("DBManager", "Start of constructor");
		this.helper = new MatchSQLiteDBHelper(this.context);
		this.db = helper.getWritableDatabase();
		
	}
	
	public void close()
	{
		this.helper.close();
	}
	
	public void open()
	{
		this.db = this.helper.getWritableDatabase();
	}



	public void addMatch(Match match)
	{
		if(matchExists(match) == -1)
		{
			// this is a key value pair holder used by android's SQLite functions
			ContentValues values = new ContentValues();
		 
			// this is how you add a value to a ContentValues object
			// we are passing in a key string and a value string each time
			values.put(TABLE_ROW_HOME_TEAM, match.getHomeTeam());
			values.put(TABLE_ROW_AWAY_TEAM, match.getAwayTeam());
			values.put(TABLE_ROW_HOME_SCORE, match.getHomeScore());
			values.put(TABLE_ROW_AWAY_SCORE, match.getAwayScore());
			values.put(TABLE_ROW_MINUTE, match.getMinute());
			values.put(TABLE_ROW_TYPE, match.getType());
			values.put(TABLE_ROW_LEAGUE, match.getLeague());
		 
			// ask the database object to insert the new data 
			try
			{
				db.insert(TABLE_NAME, null, values);
			}
			catch(Exception e)
			{
				Log.e("DB ERROR", e.toString()); // prints the error message to the log
				e.printStackTrace(); // prints the stack trace to the log
			}
		}
		else
		{
			updateMatch(matchExists(match), match);
		}
	}
	
	public void updateMatch(long rowID, Match match)
	{
		// this is a key value pair holder used by android's SQLite functions
		ContentValues values = new ContentValues();
		values.put(TABLE_ROW_HOME_TEAM, match.getHomeTeam());
		values.put(TABLE_ROW_AWAY_TEAM, match.getAwayTeam());
		values.put(TABLE_ROW_HOME_SCORE, match.getHomeScore());
		values.put(TABLE_ROW_AWAY_SCORE, match.getAwayScore());
		values.put(TABLE_ROW_MINUTE, match.getMinute());
		values.put(TABLE_ROW_TYPE, match.getType());
		values.put(TABLE_ROW_LEAGUE, match.getLeague());
	 
		// ask the database object to update the database row of given rowID
		try 
		{
			db.update(TABLE_NAME, values, TABLE_ROW_ID + "=" + rowID, null);
		}
		catch (Exception e)
		{
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
	}
	
	public Match getMatch(long rowID)
	{
		Match temp = new Match();
		Cursor cursor = null;
	 
		try
		{
		    // this method call is spread out over seven lines as a personal preference
			cursor = db.query
			(
					TABLE_NAME,
					new String[] { TABLE_ROW_ID, TABLE_ROW_HOME_TEAM, TABLE_ROW_AWAY_TEAM, TABLE_ROW_HOME_SCORE, TABLE_ROW_AWAY_SCORE, TABLE_ROW_MINUTE, TABLE_ROW_TYPE, TABLE_ROW_LEAGUE },
					TABLE_ROW_ID + "=" + rowID,
					null, null, null, null, null
			);
			cursor.moveToNext();
			do
			{
				temp.setId(cursor.getInt(0));
				temp.setHomeTeam(cursor.getString(1));
				temp.setAwayTeam(cursor.getString(2));
				temp.setHomeScore(cursor.getInt(3));
				temp.setAwayScore(cursor.getInt(4));
				temp.setMinute(cursor.getInt(5));
				temp.setType(cursor.getString(6));
				temp.setLeague(cursor.getString(7));
			}
			// move the cursor's pointer up one position.
			while (cursor.moveToNext());
		}
		catch (SQLException e) 
		{
			Log.e("DB ERROR", e.toString());
			e.printStackTrace();
		}
		
		cursor.close();
	 
		return temp;
	}
	
	public void clearDatabase()
	{
		db.execSQL("DELETE FROM " + TABLE_NAME);
		db.execSQL("DELETE FROM sqlite_sequence where name='"+ TABLE_NAME +"'"); 
		db.execSQL("VACUUM");
	}
	
	public int matchExists(Match match) {
		   Cursor cursor = db.rawQuery("SELECT * FROM "+ TABLE_NAME +" WHERE "+ TABLE_ROW_HOME_TEAM +"='"+ match.getHomeTeam() + "'" +
		   		"AND "+ TABLE_ROW_AWAY_TEAM +"='"+ match.getAwayTeam() +"'", null);
		   
		   boolean exists = (cursor.getCount() > 0);
		   
		   
		   int pos = -1;
		   
		   if(exists)
		   {
			   cursor.moveToNext();
			   pos = cursor.getInt(0);
		   }
		   
		   cursor.close();
		   
		   return pos;
		}
	
	public ArrayList<Match> getAllMatches(String type, String league)
	{
		// create an ArrayList that will hold all of the data collected from
		// the database.
		ArrayList<Match> matches = new ArrayList<Match>();
	 
		// this is a database call that creates a "cursor" object.
		// the cursor object store the information collected from the
		// database and is used to iterate through the data.
		Cursor cursor;
		String where;
		
		if(type.equalsIgnoreCase("all") && league.equalsIgnoreCase("all"))
		{
			where = null;
		}
		
		else
		{
			where = TABLE_ROW_TYPE + " = '"+ type +"' AND "+ TABLE_ROW_LEAGUE +" = '" + league +"'";
		}
	 
		try
		{
			// ask the database object to create the cursor.
			cursor = db.query(
					TABLE_NAME,
					new String[] { TABLE_ROW_ID, TABLE_ROW_HOME_TEAM, TABLE_ROW_AWAY_TEAM, TABLE_ROW_HOME_SCORE, TABLE_ROW_AWAY_SCORE, TABLE_ROW_MINUTE, TABLE_ROW_TYPE, TABLE_ROW_LEAGUE },
					where, null, null, null, null
			);
	 
			// move the cursor's pointer to position zero.
			cursor.moveToFirst();
	 
			// if there is data after the current cursor position, add it
			// to the ArrayList.
			if (!cursor.isAfterLast())
			{
				do
				{
					Match temp = new Match();
	 
					temp.setId(cursor.getInt(0));
					temp.setHomeTeam(cursor.getString(1));
					temp.setAwayTeam(cursor.getString(2));
					temp.setHomeScore(cursor.getInt(3));
					temp.setAwayScore(cursor.getInt(4));
					temp.setMinute(cursor.getInt(5));
					temp.setType(cursor.getString(6));
					temp.setLeague(cursor.getString(7));
					
					matches.add(temp);
				}
				// move the cursor's pointer up one position.
				while (cursor.moveToNext());
			}
		}
		catch (SQLException e)
		{
			Log.e("DB Error", e.toString());
			e.printStackTrace();
		}
	 
		// return the ArrayList that holds the data collected from
		// the database.
		return matches;
	}
 
	// the beginnings our SQLiteOpenHelper class
	private class MatchSQLiteDBHelper extends SQLiteOpenHelper
	{

		public MatchSQLiteDBHelper(Context context) {
			
			super(context, DB_NAME, null, DB_VERSION);

			Log.v("DBHelper", "End of constructor");
			// TODO Auto-generated constructor stub
		}
		
		@Override
        public synchronized void close() {
            if(db != null){
                db.close();
            super.close();
            }   
        }

		@Override
		public void onCreate(SQLiteDatabase db) {
			String newTableQueryString = 	
				"CREATE TABLE " +
				TABLE_NAME +
				" (" +
				TABLE_ROW_ID + " integer primary key autoincrement not null," +
				TABLE_ROW_HOME_TEAM + " text," +
				TABLE_ROW_AWAY_TEAM + " text," +
				TABLE_ROW_HOME_SCORE + " integer," +
				TABLE_ROW_AWAY_SCORE + " integer," +
				TABLE_ROW_MINUTE + " integer," +
				TABLE_ROW_TYPE + " text," +
				TABLE_ROW_LEAGUE + " text" +
				");";
		 
			// execute the query string to the database.
			db.execSQL(newTableQueryString);
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			
			db.execSQL("DROP TABLE IF EXISTS "+ TABLE_NAME +"");

			
		}
	}

}
